package fmap.dao;

import java.sql.Connection;
import java.sql.Statement;

/**
 * CreateDb.java
 * Created on 3 de abril de 2006, 23:39
 *
 * Crea la BD "fmap",
 * tenemos que averiguar si la BD esta creada y sino lo esta crearla
 *
 * @author  Javier Sanroman Araujo
 * @version $Revision: 1.1
 */
public class CreateDb
{
    public CreateDb ()
    {
    }
    
    public boolean createTableNode ( int idTable )
    {        
        DerbyDao.dbProperties.put ("create", "true");
        
        
        try
        {
        Connection conn = ConnectionDao.getConnectionCreate ();
            Statement s = conn.createStatement ();
            
            
            
            
            
            /** La clave de la tabla node debe ser idNode+idMapF */
            s.execute (
                    "CREATE TABLE fmap.node"+idTable+  
                    "(" +
                    "idNode INTEGER NOT NULL," +
                    "idMapF INTEGER NOT NULL," +
                    "idParent INTEGER," +
                    "idCollection INTEGER NOT NULL," +
                    "name VARCHAR(255) NOT NULL," +
                    "descrip LONG VARCHAR," +
                    "icon VARCHAR(255)," +
                    "size INTEGER," +
                    "path LONG VARCHAR," +
                    "type INTEGER NOT NULL," +
                    "date DATE," +
                    "keyWord LONG VARCHAR," +
                    "idTable INTEGER NOT NULL," +
                    "idTableSub INTEGER NOT NULL," +
                    "idTableParent INTEGER NOT NULL," +
                    "UNIQUE (idNode, idMapF))"
                    );
            
            s.execute ("CREATE INDEX id ON fmap.node"+idTable+"(idNode, idMapF)");

            
            
            /** La clave de la tabla node debe ser idNode+idMapF */
            
            s.executeUpdate (
                    "CREATE TABLE fmap.nodeResultSearch"+idTable+
                    "(" +
                    "idNode INTEGER NOT NULL," +
                    "idMapF INTEGER NOT NULL," +
                    "idParent INTEGER," +
                    "idCollection INTEGER NOT NULL," +
                    "name VARCHAR(255) NOT NULL," +
                    "descrip LONG VARCHAR," +
                    "icon VARCHAR(255)," +
                    "size INTEGER," +
                    "path LONG VARCHAR," +
                    "type INTEGER NOT NULL," +
                    "date DATE," +
                    "keyWord LONG VARCHAR," +
                    "nameFound VARCHAR(1)," +
                    "idTable INTEGER NOT NULL," +
                    "idTableSub INTEGER NOT NULL," +
                    "idTableParent INTEGER NOT NULL," +
                    "UNIQUE (idNode, idMapF))"
                    );
            
            s.execute ("CREATE INDEX id ON fmap.nodeResultSearch"+idTable+" (idNode, idMapF)");

            
            
            s.close ();
        }
        catch (Throwable e)
        {
            System.out.println ( e.toString () + " ERROR: Creating database ");
            return false;
            
        }
        
        
        
        DerbyDao.dbProperties.remove ("create");
        
        return true;
    }
    
    public boolean createDatabase ()
    {
        boolean bCreated = false;
        
        DerbyDao.dbProperties.put ("create", "true");
        
        bCreated = createTables ( ConnectionDao.getConnectionCreate () );
        
        DerbyDao.dbProperties.remove ("create");
        
        this.createTableNode (1);
        
        return bCreated;
    }
    
    
    
    public boolean createTables (Connection conn)
    {
        try
        {
            Statement s = conn.createStatement ();
            
            
            s.executeUpdate (
                    "CREATE TABLE fmap.tableNode" +
                    "(" +
                    "idTable INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1)," +
                    "numNode INTEGER," +
                    "UNIQUE (idTable))"
                    );

            s.executeUpdate ("INSERT INTO fmap.tableNode (numNode) VALUES(0)");



            s.executeUpdate (
                    "CREATE TABLE fmap.tableNodeMapF" +
                    "(" +
                    "idTable INTEGER NOT NULL," +
                    "idMapF INTEGER NOT NULL," +
                    "UNIQUE (idTable,idMapF))"
                    );



            s.executeUpdate (
                    "CREATE TABLE fmap.collection" +
                    "(" +
                    "idCollection INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1)," +
                    "name VARCHAR(255) NOT NULL," +
                    "descrip LONG VARCHAR," +
                    "UNIQUE (idCollection))"
                    );
            
            s.execute ("CREATE INDEX idCollection ON fmap.collection(idCollection)");
            
            s.execute ("INSERT INTO fmap.collection (name,descrip) VALUES('prueba','descripcion de prueba')");
            
            s.execute (
                    "CREATE TABLE fmap.mapF" +
                    "(" +
                    "idMapF INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1)," +
                    "idCollection INTEGER NOT NULL," +
                    "name VARCHAR(255) NOT NULL," +
                    "descrip LONG VARCHAR," +
                    "icon VARCHAR(255)," +
                    "nNod INTEGER," +
                    "path LONG VARCHAR," +
                    "idTable INTEGER," +
                    "idCat INTEGER," +
                    "UNIQUE (idMapF))"
                    );
            
            s.execute ("CREATE INDEX idMapF ON fmap.mapF(idMapF)");
            
            s.execute (
                    "CREATE TABLE fmap.mapFResultSearch" +
                    "(" +
                    "idMapF INTEGER NOT NULL," +
                    "idResult INTEGER NOT NULL," +
                    "idCollection INTEGER NOT NULL," +
                    "name VARCHAR(255) NOT NULL," +
                    "descrip LONG VARCHAR," +
                    "icon VARCHAR(255)," +
                    "nNod INTEGER," +
                    "path LONG VARCHAR," +
                    "temp VARCHAR(1)," +
                    "nResult INTEGER," +
                    "idTable INTEGER," +
                    "UNIQUE (idMapF,idResult))"
                    );
            
            s.execute ("CREATE INDEX id ON fmap.mapFResultSearch(idMapF, idResult)");
             

            s.execute (
                    "CREATE TABLE fmap.cat" +
                    "(" +
                    "idCat INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1)," +
                    "nameCat VARCHAR(255) NOT NULL," +
                    "UNIQUE (idCat))"
                    );
            
            s.execute ("CREATE INDEX id ON fmap.cat(idCat)");

            
            s.execute (
                    "CREATE TABLE fmap.extension" +
                    "(" +
                    "extension VARCHAR(10) NOT NULL," +
                    "image VARCHAR(255)," +
                    "UNIQUE (extension))"
                    );
            
            s.execute ("CREATE INDEX id ON fmap.extension(extension)");

            s.execute (
                    "CREATE TABLE fmap.groupFile" +
                    "(" +
                    "idGroupFile INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1)," +
                    "name VARCHAR(255)," +
                    "extensions LONG VARCHAR," +
                    "UNIQUE (idGroupFile))"
                    );

            s.execute ("CREATE INDEX id ON fmap.groupFile(idGroupFile)");


            s.close ();
//            conn.close ();

            System.out.println ("Tablas creadas con exito");
        }
        catch (Throwable e)
        {
            System.out.println ( e.toString () + " ERROR: Creating database ");
            return false;
            
        }
        
        return true;
    }
}
